package instrument;

import org.example.User;
import product.*;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class JdbcUtil {
    private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /* 用户操作 */
    public static List<User> loadUsers() {
        List<User> list = new ArrayList<>();
        String sql = "SELECT * FROM users";
        try (Connection c = DBUtil.getConn();
             Statement s = c.createStatement();
             ResultSet rs = s.executeQuery(sql)) {
            while (rs.next()) {
                User u = new User(
                        rs.getInt("id"),
                        rs.getString("username"),
                        rs.getString("password"),
                        rs.getString("user_type"),
                        rs.getString("telephone"),
                        sdf.parse(rs.getString("register_time")),
                        rs.getBoolean("need_change_password")
                );
                list.add(u);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void saveUsers(List<User> users) {
        String sql = "INSERT OR REPLACE INTO users VALUES(?,?,?,?,?,?,?)";
        try (Connection c = DBUtil.getConn();
             PreparedStatement ps = c.prepareStatement(sql)) {
            for (User u : users) {
                ps.setInt(1, u.getId());
                ps.setString(2, u.getUsername());
                ps.setString(3, u.getPassword());
                ps.setString(4, u.getUserType());
                ps.setString(5, u.getTelephone());
                ps.setString(6, sdf.format(u.getRegisterTime()));
                ps.setBoolean(7, u.isNeedChangePassword());
                ps.addBatch();
            }
            ps.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /* 影片操作 */
    public static List<Movie> loadMovies() {
        List<Movie> list = new ArrayList<>();
        String sql = "SELECT * FROM movies";
        try (Connection c = DBUtil.getConn();
             Statement s = c.createStatement();
             ResultSet rs = s.executeQuery(sql)) {
            while (rs.next()) {
                list.add(new Movie(
                        rs.getString("name"),
                        rs.getString("director"),
                        rs.getString("actors"),
                        rs.getString("intro"),
                        rs.getInt("duration"),
                        rs.getDouble("score"),
                        rs.getBoolean("is_showing")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void saveMovies(List<Movie> movies) {
        String sql = "INSERT OR REPLACE INTO movies VALUES(?,?,?,?,?,?,?)";
        try (Connection c = DBUtil.getConn();
             PreparedStatement ps = c.prepareStatement(sql)) {
            for (Movie m : movies) {
                ps.setString(1, m.getName());
                ps.setString(2, m.getDirector());
                ps.setString(3, m.getActors());
                ps.setString(4, m.getIntro());
                ps.setInt(5, m.getDuration());
                ps.setDouble(6, m.getScore());
                ps.setBoolean(7, m.isShowing());
                ps.addBatch();
            }
            ps.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /* 场次操作 */
    public static List<Show> loadShows(List<Movie> movies) {
        List<Show> list = new ArrayList<>();
        String sql = "SELECT * FROM shows";
        try (Connection c = DBUtil.getConn();
             Statement s = c.createStatement();
             ResultSet rs = s.executeQuery(sql)) {
            while (rs.next()) {
                Movie movie = movies.stream()
                        .filter(m -> {
                            try {
                                return m.getName().equals(rs.getString("movie_name"));
                            } catch (SQLException e) {
                                throw new RuntimeException(e);
                            }
                        })
                        .findFirst().orElse(null);
                if (movie == null) continue;
                Show sh = new Show(
                        rs.getInt("id"),
                        movie,
                        rs.getString("hall"),
                        sdf.parse(rs.getString("show_time")),
                        rs.getDouble("price"),
                        rs.getInt("row_count"),
                        rs.getInt("col_count")
                );
                sh.stringToSeats(rs.getString("seats"));
                list.add(sh);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void saveShows(List<Show> shows) {
        String sql = "INSERT OR REPLACE INTO shows VALUES(?,?,?,?,?,?,?,?)";
        try (Connection c = DBUtil.getConn();
             PreparedStatement ps = c.prepareStatement(sql)) {
            for (Show sh : shows) {
                ps.setInt(1, sh.getId());
                ps.setString(2, sh.getMovie().getName());
                ps.setString(3, sh.getHall());
                ps.setString(4, sdf.format(sh.getTime()));
                ps.setDouble(5, sh.getPrice());
                ps.setInt(6, sh.getRowCount());
                ps.setInt(7, sh.getColCount());
                ps.setString(8, sh.seatsToString());
                ps.addBatch();
            }
            ps.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /* 售票记录操作 */
    public static List<SaleRecord> loadSaleRecords(List<Show> shows) {
        List<SaleRecord> list = new ArrayList<>();
        String sql = "SELECT * FROM sale_records";
        try (Connection c = DBUtil.getConn();
             Statement s = c.createStatement();
             ResultSet rs = s.executeQuery(sql)) {
            while (rs.next()) {
                Show show = shows.stream()
                        .filter(sh -> {
                            try {
                                return sh.getId() == rs.getInt("show_id");
                            } catch (SQLException e) {
                                throw new RuntimeException(e);
                            }
                        })
                        .findFirst().orElse(null);
                if (show == null) continue;
                Seat seat = new Seat(rs.getInt("seat_row"), rs.getInt("seat_col"));
                SaleRecord sr = new SaleRecord(
                        rs.getString("ticket_id"),
                        show,
                        seat,
                        rs.getDouble("price"),
                        sdf.parse(rs.getString("sale_time"))
                );
                sr.setRefunded(rs.getBoolean("is_refunded"));
                if (rs.getString("refund_time") != null)
                    sr.setRefundTime(sdf.parse(rs.getString("refund_time")));
                sr.setRefundAmount(rs.getDouble("refund_amount"));
                list.add(sr);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void saveSaleRecords(List<SaleRecord> records) {
        String sql = "INSERT OR REPLACE INTO sale_records VALUES(?,?,?,?,?,?,?,?,?)";
        try (Connection c = DBUtil.getConn();
             PreparedStatement ps = c.prepareStatement(sql)) {
            for (SaleRecord sr : records) {
                ps.setString(1, sr.getTicketId());
                ps.setInt(2, sr.getShow().getId());
                ps.setInt(3, sr.getSeat().getRow());
                ps.setInt(4, sr.getSeat().getCol());
                ps.setDouble(5, sr.getPrice());
                ps.setString(6, sdf.format(sr.getSaleTime()));
                ps.setBoolean(7, sr.isRefunded());
                ps.setString(8, sr.getRefundTime() == null ? null : sdf.format(sr.getRefundTime()));
                ps.setDouble(9, sr.getRefundAmount());
                ps.addBatch();
            }
            ps.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}